package oa.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oa.dbcon.DBConnection;
import oa.lyk.service.Personoffice.Page;
import oa.pojo.Files;
import oa.view.FilesView;

public class FilesDao {
	public Page query(int currentPage) {
		Page page = new Page();
		page.setCurrentPage(currentPage);

		List<FilesView> viewList = new ArrayList<FilesView>();
		StringBuffer sql = new StringBuffer(
				"select v.*,row_number() " +
				"over(order by to_number(record_id)) rid from (select fil.*,dep." +
				"dept_name from files fil inner join dept dep on fil.dept_id=dep." +
				"dept_id) v where 1=1");
		StringBuffer pageSql = new StringBuffer(sql);
		pageSql.insert(0, "select * from (");
		pageSql.append(") where rid>=").append(
				(currentPage - 1) * Page.PAGE_SIZE + 1).append(" and rid<=")
				.append((currentPage - 1) * Page.PAGE_SIZE + Page.PAGE_SIZE);

		Connection con = DBConnection.getDBConnection();
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(pageSql.toString());
			while (rs.next()) {
				FilesView view = new FilesView();
				view.setRecordId(rs.getString(1));
				view.setUserId(rs.getString(2));
				view.setDeptId(rs.getString(3));
				view.setUserImg(rs.getString(4));
				view.setUserName(rs.getString(5));
				view.setUserIdnum(rs.getString(6));
				view.setMarriageStatus(rs.getString(7));
				view.setEnterDate(rs.getString(8));
				view.setStatus(rs.getString(9));
				view.setEmpWork(rs.getString(10));
				view.setDeptName(rs.getString(11));
				viewList.add(view);
			}
			rs.close();
			st.close();
			page.setFilesData(viewList);

			StringBuffer totalSql = new StringBuffer(sql);
			totalSql.insert(0, "select count(*) from (");
			totalSql.append(")");
			st = con.createStatement();

			int lineNum = 0;
			rs = st.executeQuery(totalSql.toString());
			if (rs.next()) {
				lineNum = rs.getInt(1);
			}
			int totalPage = (lineNum + Page.PAGE_SIZE - 1) / Page.PAGE_SIZE;
			rs.close();
			st.close();
			page.setTotalPage(totalPage);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return page;
	}
	public Files queryById(String id){
		Files files =null;
		String sql="select * from files where record_id="+id;
		Connection con = DBConnection.getDBConnection();
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				files = new Files();
				files.setUserId(rs.getString(2));
				files.setUserImg(rs.getString(4));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return files;
	}
}
